package com.yc.framework.utils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 对数据库操作的类
 */
public class DbUtils {
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("注册驱动失败");
        }
    }

    public static Connection getConnection() {
        try {
            return DriverManager.getConnection("jdbc:mysql://localhost:3306/px_test1", "root", "root");
        } catch (SQLException e) {
            System.out.println("获取连接对象失败");
        }
        return null;
    }

    /**
     * 对数据库表进行：增删改的操作
     *
     * @param sql
     * @return 受影响的行数
     */
    public static int executeCUD(String sql) {
        System.out.println(sql);
        int n = 0;
        Connection connection = getConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
        } catch (SQLException e) {
            System.out.println("获取命令对象失败");
        }
        try {
            n = statement.executeUpdate(sql);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("sql语句错误，请检查");
        }
        try {
            statement.close();
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return n;
    }

    /**
     * 对数据库表进行：查询的操作
     *
     * @param sql
     * @param cls 查询的记录存入的对象的类的类型
     * @param <M>
     * @return 集合
     */
    public static <M> List<M> executeR(String sql, Class<M> cls) {
        System.out.println(sql);
        List<M> ms = new ArrayList<>();
        M m = null;
        Connection connection = getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.createStatement();
        } catch (SQLException e) {
            System.out.println("获取命令对象失败");
        }
        try {
            resultSet = statement.executeQuery(sql);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("sql语句错误，请检查");
        }
        try {
            while (resultSet.next()) {
                try {
                    m = cls.newInstance();
                    Field[] fs = m.getClass().getDeclaredFields();
                    for (Field f : fs) {
                        f.setAccessible(true);
                        try {
                            f.set(m, resultSet.getObject(f.getName()));
                        } catch (Exception e) {
                            System.out.println(e.getMessage());
                            System.out.println("属性赋值出错:" + f.getName());
                        }
                    }
                    ms.add(m);
                } catch (Exception e) {
                    System.out.println(e.getMessage());
                }
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        try {
            statement.close();
            connection.close();
            resultSet.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return ms;
    }
}
